﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; 
using U9API.Pub;
using System.Data;
using U9API.JsonModel;
using Newtonsoft.Json;

namespace U9API.APIHelper
{

    public class CreditHelper : BaseHelper<JsonModel.ApiQuery>
    { 
        public List<U9API.DBHelper.Model.Customer> Resdatas { get; set; }
        public string result;

        protected override void Create(string U9constr, int operation, List<JsonModel.ApiQuery> models )
        {
            Pub.WriteLog.WriteLine(DateTime.Now.ToShortDateString() + ": CustomerHelper");
            if (models.Count == 0)
            {
                throw new Exception("查询条件不允许为空!");
            }
            JsonModel.ApiQuery model = models[0];
            StringBuilder sql = new StringBuilder();
            sql.Append(" select * , ISNULL(tmp.CreditContent_CreditLimit, 0) - ISNULL(tmp.CreditLimit_ARBalance, 0) + ISNULL(tmp.CreditLimit, 0) AS Balance  from ");
            sql.Append(" (select A5.ID, A5.Code, A7.Name ,A3.CreditContent_CreditLimit, A6.CreditLimit_ARBalance - A6.creditlimit_undoreturnmoney- A6.creditlimit_pregathering + A6.CreditLimit_ShipPlaned as CreditLimit_ARBalance,  ");            
            sql.Append(" Convert(varchar(10), A6.ModifiedOn, 121) as LastUpdateDate, A11.Name AS CreditCurrency, A6.OverdueARAmount, ");
            sql.Append(" A5.CreditPolicyName,  A10.Name as CreditLevel1, A6.MaxOverDueDays, A5.CreditPolicyCode, A8.Name as Saleser, A9.Name AS Department, ");
            sql.Append(" A2.ControlPoint, A6.CreditLimitUsed, A.ObjectStatus, ");
            sql.Append(" (Select ISNULL(sum(cc_CreditLimit),0) from CC_TempCredit A12 where A12.CreditObject = A.ID and A12.DisableDate >= CONVERT(nvarchar(10), GETDATE(),121)) AS CreditLimit ");
            sql.Append(" from  CC_CreditControlObject as A ");
            sql.Append(" left join CC_ObjectCreditPolicy as A1 on(A1.[CreditObject] = A.[ID])");
            sql.Append(" left join CC_ObjectCreditLevel as A2 on(A2.[ObjectCreditPolicy] = A1.[ID])");
            sql.Append(" left join CC_ObjectCreditLevelCurrency as A3 on(A2.[ID] = A3.[ObjectCreditLevel]) ");
            sql.Append(" left join CC_CreditPolicy as A4 on(A4.[ID] = A1.[CreditPolicy]) ");
            sql.Append(" left join[CBO_Customer] as A5 on(A.[Customer] = A5.[ID]) ");
            sql.Append(" left join CBO_Customer_Trl A7 on A7.id = A5.ID");
            sql.Append(" left join CC_CreditBalance A6 on A6.CreditObject = A.ID ");
            sql.Append(" left join CBO_Operators_Trl A8 on A8.ID = A5.Saleser ");
            sql.Append(" left join CBO_Department_Trl A9 on A9.ID = A5.Department");
            sql.Append(" left join CC_CreditLevel_Trl A10 on A10.ID = A2.CreditLevel");
            sql.Append(" left join Base_Currency_Trl A11 on A11.ID = A3.CreditLimitCurrency and A11.SysMLFlag = 'zh-CN'");
            sql.Append(" where A.[ObjectType] = 0 and A4.[CreditCM] = 0 ");

            if (!string.IsNullOrEmpty(model.id))
            {
                sql.Append(" and A5.id = " + model.id);
            }
            if (!string.IsNullOrEmpty(model.code))
            {
                sql.Append("and A5.code = '" + model.code + "'");
            }
            if (!string.IsNullOrEmpty(model.startdate))
            {
                sql.Append(" and (a.CreatedOn >='" + model.startdate + "' or a.ModifiedOn >='" + model.startdate +"')");
            }
            if (!string.IsNullOrEmpty(model.enddate))
            {
                sql.Append(" and (a.CreatedOn <='" + model.enddate + "' or a.ModifiedOn <='" + model.enddate + "')");
            }
            sql.Append(" ) tmp");
            DataSet ds = DbHelperSQL.Query(sql.ToString(), U9constr);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            { 
                result = JsonConvert.SerializeObject(ds.Tables[0]);
            }
            else
            {
                throw new Exception("没有查到匹配的客户信用记录!");
                
            }
        }
         
    }
}
